package com.wugui.datax.admin.service.impl;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.google.common.collect.Lists;
import com.wugui.datax.admin.dto.ColumnTypeDto;
import com.wugui.datax.admin.dto.OracleTableDto;
import com.wugui.datax.admin.dto.TableNameDto;
import com.wugui.datax.admin.entity.GenTableColumn;
import com.wugui.datax.admin.entity.JobDatasource;
import com.wugui.datax.admin.service.DatasourceQueryService;
import com.wugui.datax.admin.service.JobDatasourceService;
import com.wugui.datax.admin.tool.query.BaseQueryTool;
import com.wugui.datax.admin.tool.query.HBaseQueryTool;
import com.wugui.datax.admin.tool.query.MongoDBQueryTool;
import com.wugui.datax.admin.tool.query.QueryToolFactory;
import com.wugui.datax.admin.util.ExcelUtils;
import com.wugui.datax.admin.util.JdbcConstants;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.sql.SQLException;
import java.util.*;

/**
 * datasource query
 *
 * @author zhouhongfa@gz-yibo.com
 * @ClassName JdbcDatasourceQueryServiceImpl
 * @Version 1.0
 * @since 2019/7/31 20:51
 */
@Service
public class DatasourceQueryServiceImpl implements DatasourceQueryService {

    protected static final Logger logger = LoggerFactory.getLogger(DatasourceQueryServiceImpl.class);

    @Autowired
    private JobDatasourceService jobDatasourceService;

    @Override
    public List<String> getDBs(Long id) throws IOException {
        //获取数据源对象
        JobDatasource datasource = jobDatasourceService.getById(id);
        return new MongoDBQueryTool(datasource).getDBNames();
    }


    @Override
    public List<String> getTables(Long id, String tableSchema) throws IOException {
        //获取数据源对象
        JobDatasource datasource = jobDatasourceService.getById(id);
        //queryTool组装
        if (ObjectUtil.isNull(datasource)) {
            return Lists.newArrayList();
        }
        if (JdbcConstants.HBASE.equals(datasource.getDatasource())) {
            return new HBaseQueryTool(datasource).getTableNames();
        } else if (JdbcConstants.MONGODB.equals(datasource.getDatasource())) {
            return new MongoDBQueryTool(datasource).getCollectionNames(datasource.getDatabaseName());
        } else {
            BaseQueryTool qTool = QueryToolFactory.getByDbType(datasource);
            return qTool.getTableNames(tableSchema);
        }
    }

    @Override
    public List<TableNameDto> getTableNameAndComment(Long datasourceId, String tableSchema) {
        //获取数据源对象
        JobDatasource datasource = jobDatasourceService.getById(datasourceId);
        BaseQueryTool qTool = QueryToolFactory.getByDbType(datasource);
        return qTool.getTableNameAndComment();
    }

    @Override
    public List<String> getTableSchema(Long id) {
        //获取数据源对象
        JobDatasource datasource = jobDatasourceService.getById(id);
        //queryTool组装
        if (ObjectUtil.isNull(datasource)) {
            return Lists.newArrayList();
        }
        BaseQueryTool qTool = QueryToolFactory.getByDbType(datasource);
        return qTool.getTableSchema();
    }

    @Override
    public List<String> getCollectionNames(long id, String dbName) throws IOException {
        //获取数据源对象
        JobDatasource datasource = jobDatasourceService.getById(id);
        //queryTool组装
        if (ObjectUtil.isNull(datasource)) {
            return Lists.newArrayList();
        }
        return new MongoDBQueryTool(datasource).getCollectionNames(dbName);
    }


    @Override
    public List<String> getColumns(Long id, String tableName) throws IOException {
        //获取数据源对象
        JobDatasource datasource = jobDatasourceService.getById(id);
        //queryTool组装
        if (ObjectUtil.isNull(datasource)) {
            return Lists.newArrayList();
        }
        if (JdbcConstants.HBASE.equals(datasource.getDatasource())) {
            return new HBaseQueryTool(datasource).getColumns(tableName);
        } else if (JdbcConstants.MONGODB.equals(datasource.getDatasource())) {
            return new MongoDBQueryTool(datasource).getColumns(tableName);
        } else {
            BaseQueryTool queryTool = QueryToolFactory.getByDbType(datasource);
            return queryTool.getColumnNames(tableName, datasource.getDatasource());
        }
    }

    @Override
    public List<GenTableColumn> getTableColumns(Long id, String tableName) throws IOException {
        //获取数据源对象
        JobDatasource datasource = jobDatasourceService.getById(id);
        BaseQueryTool queryTool = QueryToolFactory.getByDbType(datasource);
        return queryTool.getTableColumnNames(tableName, datasource.getDatasource());
    }

    @Override
    public List<String> getColumnsByQuerySql(Long datasourceId, String querySql) throws SQLException {
        //获取数据源对象
        JobDatasource jdbcDatasource = jobDatasourceService.getById(datasourceId);
        //queryTool组装
        if (ObjectUtil.isNull(jdbcDatasource)) {
            return Lists.newArrayList();
        }
        BaseQueryTool queryTool = QueryToolFactory.getByDbType(jdbcDatasource);
        return queryTool.getColumnsByQuerySql(querySql);
    }

    @Override
    public String generateOracleDdl(OracleTableDto oracleTableDto) {
        StringBuilder sql = new StringBuilder();
        List<GenTableColumn> genTableColumns = generateOracleDict(oracleTableDto);
        String fullTableName = StrUtil.nullToEmpty(oracleTableDto.getFullTableName());
        String tableComment = oracleTableDto.getTableComment();
        sql.append("create table ").append(fullTableName).append("\n").append("(").append("\n");
        for (int i = 0; i < genTableColumns.size(); i++) {
            GenTableColumn genTableColumn = genTableColumns.get(i);
            String columnName = genTableColumn.getColumnName().toUpperCase();
            String columnType = genTableColumn.getColumnType();
            if ("1".equals(genTableColumn.getIsPk())) {
                sql.append("  ").append(columnName).append("     ").append(columnType).append("   not null primary key");
            } else {
                sql.append("  ").append(columnName).append("     ").append(columnType);
            }
            if (genTableColumns.size() - i != 1) {
                sql.append(",\n");
            } else {
                sql.append("\n)");
                sql.append("/\n");
            }
        }
        sql.append("comment on table ").append(fullTableName).append(" is '").append(tableComment).append("'\n/");
        for (int i = 0; i < genTableColumns.size(); i++) {
            GenTableColumn genTableColumn = genTableColumns.get(i);
            String columnName = genTableColumn.getColumnName().toUpperCase();
            String columnComment = genTableColumn.getColumnComment();
            sql.append("comment on column ").append(fullTableName).append(".").append(columnName).append(" is '").append(columnComment).append("'\n/\n");
        }
        logger.info("<<<<<mysql ddl convert oracle ddl>>>>");
        logger.info("{}", sql.toString());
        return sql.toString();
    }

    @Override
    public List<GenTableColumn> generateOracleDict(OracleTableDto oracleTableDto) {
        List<GenTableColumn> list = new ArrayList<>();
        List<GenTableColumn> tableColumnList = oracleTableDto.getTableColumnList();
        List<ColumnTypeDto> columnTypeDtoList = oracleTableDto.getColumnTypeDtoList();
        Map<String, String> columnTypeMap = new HashMap<>();
        if (CollectionUtils.isNotEmpty(columnTypeDtoList)) {
            for (ColumnTypeDto columnTypeDto : columnTypeDtoList) {
                columnTypeMap.put(columnTypeDto.getMysqlColumnType(), columnTypeDto.getOracleColumnType());
            }
        } else {
            columnTypeMap = ColumnTypeDto.defaultColumnTypeMap();
        }
        for (int i = 0; i < tableColumnList.size(); i++) {
            GenTableColumn genTableColumn = new GenTableColumn();
            GenTableColumn tableColumn = tableColumnList.get(i);
            genTableColumn.setColumnComment(tableColumn.getColumnComment());
            genTableColumn.setColumnName(tableColumn.getColumnName());
            genTableColumn.setColumnType(ColumnTypeDto.convertColumnType(tableColumn.getColumnType(), columnTypeMap));
            genTableColumn.setCharacterMaximumLength(tableColumn.getCharacterMaximumLength());
            genTableColumn.setDataType(ColumnTypeDto.convertDataType(tableColumn.getDataType()));
            genTableColumn.setIsPk(tableColumn.getIsPk());
            genTableColumn.setTableName(tableColumn.getTableName());
            list.add(genTableColumn);
        }
        return list;
    }

    @Override
    public Workbook exportOracleDict(String fileName, OracleTableDto oracleTableDto) {
        String fullTableName = oracleTableDto.getFullTableName();
        List<GenTableColumn> tableColumnList = oracleTableDto.getTableColumnList();
        //TODO 29个字符
        String sheetName = fullTableName;
        List<List<String>> excelHeadList = new ArrayList<>();
        List<String> firstRow = new ArrayList<>();
        firstRow.add("中文名称");
        firstRow.add(oracleTableDto.getTableComment());
        firstRow.add("目录");

        List<String> secondRow = new ArrayList<>();
        secondRow.add("英文名称");
        secondRow.add(oracleTableDto.getFullTableName());
        excelHeadList.add(firstRow);
        excelHeadList.add(secondRow);
        Map<String, String> cellNameMap = new LinkedHashMap<>();
        cellNameMap.put("columnName", "字段英文名");
        cellNameMap.put("columnComment", "字段中文名");
        cellNameMap.put("dataType", "字段类型");
        cellNameMap.put("characterMaximumLength", "字段长度");
        cellNameMap.put("isPk", "主键否");
        cellNameMap.put("默认值", "默认值");
        cellNameMap.put("说明", "说明");
        cellNameMap.put("说明2", "");
        cellNameMap.put("关联的表", "关联的表");
        cellNameMap.put("关联的字段", "关联的字段");

        List<Map<String, String>> dataList = new ArrayList<>();
        for (int i = 0; i < tableColumnList.size(); i++) {
            GenTableColumn genTableColumn = tableColumnList.get(i);
            Map<String, String> map = new HashMap<>(5);
            map.put("columnName", genTableColumn.getColumnName());
            map.put("columnComment", genTableColumn.getColumnComment());
            map.put("dataType", genTableColumn.getDataType());
            map.put("characterMaximumLength", genTableColumn.getCharacterMaximumLength() != null ? genTableColumn.getCharacterMaximumLength() + "" : "");
            map.put("isPk", "1".equals(genTableColumn.getIsPk()) ? "是" : "否");
            dataList.add(map);
        }
        Workbook workbook = ExcelUtils.writeExcel(fileName, sheetName, excelHeadList, cellNameMap, dataList);
        return workbook;
    }
}
